package com.fc.control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import com.fc.db.DBConnect;
import com.fc.model.Worker;
import com.fc.util.Pagination;

public class WorkerControl {
public static Pagination pageInstance = null;
	
	//add Worker method
	public static Boolean addWorker(Worker worker){
		DBConnect dbc = new DBConnect();
		String sql = "insert into worker (store_id,wages,addtime,position,idcard,name) value(?,?,?,?,?,?) ";
		dbc.prepareStatement(sql);
		try{
			dbc.setInt(1,worker.getStore_id());
			dbc.setInt(2,worker.getWages());
			dbc.setString(3, worker.getAddtime());
			dbc.setString(4,worker.getPosition());
			dbc.setString(5,worker.getIdcard());
			dbc.setString(6,worker.getName());
			dbc.executeUpdate();
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
	}
	//remove worker method
	public static Boolean deleteWorker(int id){
		DBConnect dbc = new DBConnect();
		String sql = "delete from worker where id=?";
		dbc.prepareStatement(sql);
		try{
			dbc.setInt(1,id);
			dbc.executeUpdate();
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
	}
	//update worker method
	public static Boolean updateWorker(Worker worker,int id){
		DBConnect dbc = new DBConnect();
		String sql = "update worker set position=?,wages=? where id=?";
		dbc.prepareStatement(sql);
		try{
			dbc.setString(1, worker.getPosition());
			dbc.setInt(2,worker.getWages());
			dbc.setInt(3,id);
			dbc.executeUpdate();
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
			
		}
	}
	//get worker information by id
	public static Worker getWorkerInfo(int id){
		DBConnect dbc = new DBConnect();
		String sql = "select * from worker where id=?";
		dbc.prepareStatement(sql);
		try{
			Worker worker = new Worker();
			dbc.setInt(1,id);
			ResultSet rs = dbc.executeQuery();
		if(rs.next())
		{
			worker.setStore_id(rs.getInt("store_id"));
			worker.setWages(rs.getInt("wages"));
			worker.setAddtime(rs.getString("addtime"));
			worker.setIdcard(rs.getString("idcard"));
			worker.setPosition(rs.getString("position"));
			worker.setName(rs.getString("name"));
			return worker;
		}
	}catch(SQLException e){
		e.printStackTrace();
		return null;
	}finally{
		dbc.close();
	}
	return null;
	}
	
	//get worker information by idcard
	/*public static Worker getWorkerInfo1(String idcard){
		DBConnect dbc = new DBConnect();
		String sql = "select * from worker where idcard=?";
		dbc.prepareStatement(sql);
		try{
			Worker worker = new Worker();
			dbc.setString(1,idcard);
			ResultSet rs = dbc.executeQuery();
		if(rs.next())
		{
			dbc.setInt(1, worker.getStore_id());
			dbc.setInt(2, worker.getWages());
			dbc.setString(3,worker.getAddtime());
			dbc.setInt(4, worker.getId());
			dbc.setString(5, worker.getPosition());
			return worker;
		}
	}catch(SQLException e){
		e.printStackTrace();
		return null;
	}finally{
		dbc.close();
	}
	return null;
	}*/
	
	//pagination method
	public Collection getWorker(int page,int rowsPerPage)throws SQLException,Exception{
		String sql = "select * from worker";
		pageInstance = new WorkerControlPage(sql,rowsPerPage);
		try{
			return pageInstance.getPage(page);
		}catch(SQLException e){
			e.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}
		return null;
	}
}

class WorkerControlPage extends Pagination{
	public WorkerControlPage(String sql,int rowsPerPage){
		super(sql,rowsPerPage,1);
	}
	@Override
	protected Collection<Worker> packResultSet(ResultSet rs) throws SQLException {
		Collection<Worker> workerList = new ArrayList<Worker>();
		while (rs.next()) {
			Worker worker=new Worker();
			worker.setId(rs.getInt("id"));
			worker.setIdcard(rs.getString("idcard"));
			worker.setStore_id(rs.getInt("store_id"));
			worker.setPosition(rs.getString("position"));
			worker.setAddtime(rs.getString("addtime"));
			worker.setWages(rs.getInt("wages"));
			worker.setName(rs.getString("name"));
			workerList.add(worker);
			worker = null;
		}
		return workerList;
	}
	
}
